#!/usr/local/bin/python3
# -*- coding: utf-8 -*-

"""
@File    : exp_sql.py
@Author  : different
@Time    : 2021-8-9 11:45
@Desc    : SQL语句类

"""
from expand import db
from expand.exp_data.tools.data_format import ret_max, ret_arr, ret_attr_sql, is_number


class RetSql:
    def __init__(self, pk) -> None:
        self.pk = pk
        self.conn = db.session

        # rating销量区间占比图
        self.rating_sql = f"""SELECT
            *
            FROM(
                SELECT(
                    CASE
                        WHEN ratings >= 0 AND ratings <= 50 THEN "0-50"
                        WHEN ratings > 50 AND ratings <= 100 THEN "51-100"
                        WHEN ratings > 100 AND ratings <= 300 THEN "101-300"
                        WHEN ratings > 300 AND ratings <= 500 THEN "301~500"
                        WHEN ratings > 500 AND ratings <= 1000 THEN "501~1000"
                        WHEN ratings > 1000 AND ratings <= 2000 THEN "1001~2000"
                        WHEN ratings > 2000 AND ratings <= 5000 THEN "2001~5000"
                        WHEN ratings > 5000 AND ratings <= 10000 THEN "5001~10000"
                        ELSE "10000+"
                    END)
                AS name,
                CAST(sum(sale) AS SIGNED) AS value
                FROM data_source_content
                WHERE table_id = {self.pk}
                GROUP BY name
                ) bb
            ORDER BY
                - name"""
        # 上架时间销量占比
        self.add_time_sql = f"""
        SELECT
        *
        FROM(
            SELECT
            (CASE
                WHEN add_time >= 0 AND add_time <= 30 THEN "6.一个月"
                WHEN add_time > 30 AND add_time <= 90 THEN "5.三个月"
                WHEN add_time > 90 AND add_time <= 180 THEN "4.半年"
                WHEN add_time > 180 AND add_time <= 365 THEN "3.一年"
                WHEN add_time > 365 AND add_time <= 730 THEN "2.两年"
                ELSE "1.两年以上"
            END)
            AS name,
            CAST(sum(sale) AS SIGNED) AS value
        FROM
           (SELECT sale, DATEDIFF( NOW(), shelf_date) AS add_time
            FROM data_source_content WHERE table_id = {self.pk} ) t1
        GROUP BY name
        ) bb
        ORDER BY
        - name desc """
        # 评分区间销量占比
        self.score_sql = f"""
        SELECT
            *
        FROM(
            SELECT(
                CASE
                    WHEN rating >= 0 AND rating <= 3.5 THEN "0-3.5"
                    WHEN rating > 3.5 AND rating <= 4 THEN "3.5-4.0"
                    WHEN rating > 4 AND rating <= 4.5 THEN "4.0-4.5"
                    WHEN rating > 4.5 AND rating < 5 THEN "4.5-5.0"
                    ELSE "5.0"
                END)
            AS name,
            CAST(sum(sale) AS SIGNED) AS value
            FROM data_source_content
            WHERE table_id = {self.pk}
            GROUP BY name)  bb
        ORDER BY
            - name"""
        # 评分星级个数占比
        self.rating_stars_sql = f"""
        SELECT
        *
        FROM
        (SELECT
               (CASE
                    WHEN rating >= 0 AND rating < 1 THEN "10.半星"
                    WHEN rating >= 1 AND rating < 1.3 THEN "9.1星"
                    WHEN rating >= 1.3 AND rating < 1.8 THEN "8.1星半"
                    WHEN rating >= 1.8 AND rating < 2.3 THEN "7.2星"
                    WHEN rating >= 2.3 AND rating < 2.8 THEN "6.2星半"
                    WHEN rating >= 2.8 AND rating < 3.3 THEN "5.3星"
                    WHEN rating >= 3.3 AND rating < 3.8 THEN "4.3星半"
                    WHEN rating >= 3.8 AND rating < 4.3 THEN "3.4星"
                    WHEN rating >= 4.3 AND rating < 4.8 THEN "2.4星半"
                    WHEN rating >= 4.8 AND rating <= 5 THEN "1.5星"
                END)
            AS name,
            COUNT(*) as value
            FROM data_source_content
            WHERE table_id = {self.pk}
            GROUP BY name
        ) bb
        ORDER BY
        - name desc"""
        # top5 品牌销量占比
        self.top5_sql = f"""
        SELECT
            brand name,
            CAST(sum( sale ) AS SIGNED) AS value
        FROM
            data_source_content
        WHERE
            table_id = {self.pk}
        GROUP BY
            brand
        ORDER BY
            value DESC
        LIMIT
            5
        """
        # 最大价格
        self.min_price = f"""SELECT MIN(price) FROM `data_source_content` WHERE table_id = {self.pk}"""
        # 最小价格
        self.max_price = f"""SELECT MAX(price) FROM `data_source_content` WHERE table_id = {self.pk}"""
        # 表格title
        self.text_title_sql = f"""SELECT title FROM `data_source_title` WHERE id = {self.pk}"""
        # 主属性数据
        self.sheet_sql = f"""SELECT
            asin,
            product_link,
            image_url,
            price,
            brand,
            seller_type,
            page_rank,
            shelf_date,
            big_category,
            big_category_rank,
            sub_category_1,
            sub_category_1_rank,
            rank_days_30,
            ratings,
            rating,
            sale,
            attr
        FROM
            data_source_content
        WHERE
            table_id = {self.pk}"""
        # 获取属性
        self.goods_attr = f'''SELECT * FROM data_source_content WHERE table_id = {self.pk} LIMIT 1'''

    # 价格区间销量占比
    def price_sql(self, arr):
        return f"""SELECT
        *
        FROM(
        SELECT(
                CASE
                    WHEN price >= {arr[0]} AND price <= {arr[1]} THEN "{arr[0]}~{arr[1]}"
                    WHEN price >= {arr[1] + 0.01} AND price <= {arr[2]} THEN "{arr[1]}~{arr[2]}"
                    WHEN price >= {arr[2] + 0.01} AND price <= {arr[3]} THEN "{arr[2]}~{arr[3]}"
                    WHEN price >= {arr[3] + 0.01} AND price <= {arr[4]} THEN "{arr[3]}~{arr[4]}"
                    WHEN price >= {arr[4] + 0.01} AND price <= {arr[5]} THEN "{arr[4]}~{arr[5]}"
                    WHEN price >= {arr[5] + 0.01} THEN "{arr[5]}~{arr[6]}"
                END)
        AS name ,
        CAST(sum(sale) AS SIGNED) AS value
        FROM data_source_content
        WHERE table_id = {self.pk}
        GROUP BY name
    )  bb
    ORDER BY
     - name desc"""

    # 销量占比
    def sale_percentage_sql(self, sale):
        return f"""
        SELECT
            CONCAT( ROUND( {sale} / CAST( sum( sale ) AS SIGNED ) * 100, 2 ), '%' )
        FROM
            data_source_content
        WHERE
            table_id = {self.pk}
        """

    # 交叉表数据查询
    def cross_data_sql(self, value, is_main_multi=False, is_multi=False):
        """
        四种情况
        :param value: 查询字典 {主属性:[], 次属性:[]}
        :param is_main_multi: 判定主属性是否为多属性分组 默认为False
        :param is_multi: 判定次属性是否为多属性分组 默认为False
        :return: 返回查询sql
        """

        main_attr_key, other_attr_key = value
        main_attr_val = value[main_attr_key]
        other_attr_val = value[other_attr_key]
        # 主属性和次属性都不为多属性分组
        if not is_main_multi and not is_multi:
            main_emt1, main_emt2 = ret_max(*main_attr_val, is_sql=True)
            other_emt1, other_emt2 = ret_max(*other_attr_val, is_sql=True)
            sql = f"""SELECT
                    IF(count(*) = 0, NULL, COUNT(*)) as count,
                    CAST(SUM( sale ) AS SIGNED) AS sum,
                    CONCAT( ROUND( SUM( sale ) / ( SELECT SUM( sale ) FROM data_source_content WHERE table_id = {self.pk} ) * 100, 2 ), '%' ) as percentage,
                    CAST( SUM( sale )  / COUNT(*)  AS SIGNED) avg   
                FROM
                    data_source_content 
                WHERE
                    table_id = {self.pk} 
                    AND attr -> '$."{main_attr_key}"' >= {main_emt1}
                    AND attr -> '$."{main_attr_key}"' <= {main_emt2} 
                    AND attr -> '$."{other_attr_key}"' >= {other_emt1} 
                    AND attr -> '$."{other_attr_key}"' <= {other_emt2}
                    """
            return sql
        # 主属性为多属性分组
        elif is_main_multi and not is_multi:
            main_attr_val = ret_arr(main_attr_val)
            main_attr_sql = ret_attr_sql(main_attr_key, main_attr_val)

            other_emt1, other_emt2 = ret_max(*other_attr_val, is_sql=True)
            sql = f"""SELECT
                        IF(count(*) = 0, NULL, COUNT(*)) as count,
                        CAST(SUM( sale ) AS SIGNED) AS sum,
                        CONCAT( ROUND( SUM( sale ) / ( SELECT SUM( sale ) FROM data_source_content WHERE table_id = {self.pk} ) * 100, 2 ), '%' ) as percentage,
                        CAST( SUM( sale )  / COUNT(*)  AS SIGNED) avg   
                    FROM
                        data_source_content 
                    WHERE
                        table_id = {self.pk} 
                        AND ({main_attr_sql})
                        AND attr -> '$."{other_attr_key}"' >= {other_emt1} 
                        AND attr -> '$."{other_attr_key}"' <= {other_emt2} 
                        """
            return sql
        # 次属性为多属性分组
        elif not is_main_multi and is_multi:
            other_attr_val = ret_arr(other_attr_val)
            other_attr_sql = ret_attr_sql(other_attr_key, other_attr_val)

            main_emt1, main_emt2 = ret_max(*main_attr_val, is_sql=True)
            sql = f"""SELECT
                        IF(count(*) = 0, NULL, COUNT(*)) as count,
                        CAST(SUM( sale ) AS SIGNED) AS sum,
                        CONCAT( ROUND( SUM( sale ) / ( SELECT SUM( sale ) FROM data_source_content WHERE table_id = {self.pk} ) * 100, 2 ), '%' ) as percentage,
                        CAST( SUM( sale )  / COUNT(*)  AS SIGNED) avg   
                    FROM
                        data_source_content 
                    WHERE
                        table_id = {self.pk} 
                        AND  attr -> '$."{main_attr_key}"' >= {main_emt1}
                        AND attr -> '$."{main_attr_key}"' <= {main_emt2} 
                        AND ({other_attr_sql})
                        """
            return sql
        # 主属性和次属性都为多属性分组
        elif is_main_multi and is_multi:
            main_attr_val = ret_arr(main_attr_val)
            main_attr_sql = ret_attr_sql(main_attr_key, main_attr_val)

            other_attr_val = ret_arr(other_attr_val)
            other_attr_sql = ret_attr_sql(other_attr_key, other_attr_val)
            sql = f"""SELECT
                        IF(count(*) = 0, NULL, COUNT(*)) as count,
                        CAST(SUM( sale ) AS SIGNED) AS sum,
                        CONCAT( ROUND( SUM( sale ) / ( SELECT SUM( sale ) FROM data_source_content WHERE table_id = {self.pk} ) * 100, 2 ), '%' ) as percentage,
                        CAST( SUM( sale )  / COUNT(*)  AS SIGNED) avg   
                    FROM
                        data_source_content 
                    WHERE
                        table_id = {self.pk} 
                        AND ({main_attr_sql})
                        AND ({other_attr_sql})
                        """
            return sql
        return None

    # 属性总计
    def total_sql(self, value, is_main_multi=False):
        if not is_main_multi:
            k, = value
            a, b = value[k]
            a, b = ret_max(a, b, is_sql=True)
            sql = f"""
            SELECT
                IF(count(*) = 0, NULL, COUNT(*)) as count,
                CAST(SUM( sale ) AS SIGNED) AS sum,
                CONCAT( ROUND( SUM( sale ) / ( SELECT SUM( sale ) FROM data_source_content WHERE table_id = {self.pk} ) * 100, 2 ), '%' ) as percentage,
                CAST( SUM( sale )  / COUNT(*)  AS SIGNED) avg   
            FROM
                data_source_content 
            WHERE
                table_id = {self.pk}
                AND attr -> '$."{k}"' >= {a}
                AND attr -> '$."{k}"' <= {b} """
            return sql
        k, = value
        k_attr = ret_arr(value[k])
        attr_sql = ret_attr_sql(k, k_attr)
        sql = f"""
        SELECT
            IF(count(*) = 0, NULL, COUNT(*)) as count,
            CAST(SUM( sale ) AS SIGNED) AS sum,
            CONCAT( ROUND( SUM( sale ) / ( SELECT SUM( sale ) FROM data_source_content WHERE table_id = {self.pk} ) * 100, 2 ), '%' ) as percentage,
            CAST( SUM( sale )  / COUNT(*)  AS SIGNED) avg   
        FROM
            data_source_content 
        WHERE
            table_id = {self.pk}
            AND ({attr_sql}) """
        return sql

    def total_multi_attr_sql(self, val):
        k, = val
        k_attr = ret_arr(val[k])
        attr_sql = ret_attr_sql(k, k_attr)
        sql = f"""
        SELECT
            IF(count(*) = 0, NULL, COUNT(*)) as count,
            CAST(SUM( sale ) AS SIGNED) AS sum,
            CONCAT( ROUND( SUM( sale ) / ( SELECT SUM( sale ) FROM data_source_content WHERE table_id = {self.pk} ) * 100, 2 ), '%' ) as percentage,
            CAST( SUM( sale )  / COUNT(*)  AS SIGNED) avg   
        FROM
            data_source_content 
        WHERE
            table_id = {self.pk}
            AND ({attr_sql}) """
        return sql

    def all_total_sql(self):
        return f"""
        SELECT
            IF(count(*) = 0, NULL, COUNT(*)) as count,
            CAST(SUM( sale ) AS SIGNED) AS sum,
            CONCAT( ROUND( SUM( sale ) / ( SELECT SUM( sale ) FROM data_source_content WHERE table_id = {self.pk} ) * 100, 2 ), '%' ) as percentage,
            CAST( SUM( sale )  / COUNT(*)  AS SIGNED) avg   
        FROM
            data_source_content 
        WHERE
            table_id = {self.pk}"""

    def group_image(self, value):
        attr_name, = value
        a = value[attr_name][0]
        if not is_number(a):
            a = '\'' + a + '\''
        return f"""
        SELECT
            image_url 
        FROM
            data_source_content 
        WHERE
            TABLE_ID = {self.pk} 
            AND ATTR -> '$."{attr_name}"' = {a} 
        LIMIT 1"""
